import json
import pymysql
from datetime import datetime

# Your JSON data
data = {
    "100000": {
        "710000": "台湾省",
        "810000": "港澳"
    },
    "710000": {
        "710100": "台湾省"
    },
    "710100": {
        "710101": "金门",
        "710102": "连江",
        "710103": "苗栗",
        "710104": "南投",
        "710105": "澎湖",
        "710106": "屏东",
        "710107": "台东",
        "710108": "台中",
        "710109": "台南",
        "710110": "台北",
        "710111": "桃园",
        "710112": "云林",
        "710113": "新北",
        "710114": "彰化",
        "710115": "嘉义",
        "710116": "新竹",
        "710117": "花莲",
        "710118": "宜兰",
        "710119": "高雄",
        "710120": "基隆"
    },
    "810000": {
        "810100": "香港特别行政区",
        "810200": "澳门特别行政区"
    },
    "810100": {
        "810101": "中西区",
        "810102": "东区",
        "810103": "九龙城区",
        "810104": "观塘区",
        "810105": "深水埗区",
        "810106": "湾仔区",
        "810107": "黄大仙区",
        "810108": "油尖旺区",
        "810109": "离岛区",
        "810110": "葵青区",
        "810111": "北区",
        "810112": "西贡区",
        "810113": "沙田区",
        "810114": "屯门区",
        "810115": "大埔区",
        "810116": "荃湾区",
        "810117": "元朗区",
        "810118": "香港",
        "810119": "九龙",
        "810120": "新界"
    },
    "810200": {
        "810200": "澳门特别行政区",
        "810202": "澳门半岛",
        "810203": "凼仔",
        "810204": "路凼城",
        "810205": "路环"
    }
}

# Connect to MySQL database
# conn = pymysql.connect(host='localhost', user='your_username', password='your_password', database='your_database')
# cursor = conn.cursor()


# Recursive function to traverse JSON and generate SQL insert statements
def generate_insert_sql(data, parent_code=None, parent_name='', level=1, code_path=''):
    sql_statements = []

    for code, name in data.items():
        short_name = name
        merge_name = f"{parent_name}/{name}" if parent_name else name
        pinyin = ''

        # If the current value is a dictionary, get its name as the short name
        if isinstance(name, dict):
            short_name = list(name.values())[0]
            merge_name = f"{parent_name}/{short_name}" if parent_name else short_name

        # Generate SQL insert statement
        sql = f"""
        INSERT INTO `ifssc-cus`.`sys_region` (
            `code`,
            `name`,
            `parent_code`,
            `short_name`,
            `level`,
            `merge_name`,
            `create_time`,
            `update_time`,
            `code_path`
        )
        VALUES (
            '{code}',
            '{name}',
            '{parent_code}',
            NULL,
            {level},
            '{merge_name}',
            '{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}',
            '{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}',
            '{code_path}|{code}'
        );
        """

        sql_statements.append(sql)

        # If the current value is a dictionary, recursively call the function
        if isinstance(name, dict):
            sql_statements.extend(generate_insert_sql(name, code, merge_name, level + 1, f"{code_path}|{code}"))

    return sql_statements


# Generate SQL statements
sql_statements = generate_insert_sql(data)

# Execute SQL statements
for sql in sql_statements:
    print(sql)
    # try:
    #     cursor.execute(sql)
    #     conn.commit()
    # except Exception as e:
    #     print(f"Error executing SQL: {e}")
    #     conn.rollback()

# Close the connection
# cursor.close()
# conn.close()

print("SQL insertion completed!")
